drop database if exists mysqlDB;
create database mysqlDB;
use mysqlDB;
#####################   WEBר  ################################
create table members(
       ID         varchar(15)      primary key,
       open       enum('true','false')  not null,
       psw        varchar(8)       not null,
       sex        enum('','Ů')  not null,
       birthday   int(2)           null,
       birthmonth  int(2)          null,
       birthyear   int(4)          null,
       email      varchar(40)      null,
       score      int              null,
       grade      varchar(10)      null,
       lasttime   timestamp        not null,
       registtime  timestamp       not null,
       realname   varchar(20)      null,
       province   varchar(15)      null,
       city       varchar(15)      null,
       mobile     varchar(15)      null,
       address    varchar(20)      null,
       height     int              null,
       weight     int              null,
       workedat   varchar(10)      null,
       post       varchar(10)      null,
       hobby      varchar(20)      null,
       lookingfor varchar(10)      null,
       introduction  varchar(500)  null,
       locked     enum('false','true') not null
);
insert into members set ID='admin',
                      psw='admin',
                      sex='',
                      birthday=01,
                      birthmonth=01,
                      birthyear=1980,
                      email='',
                      grade='վ',
                      score=0,
                      lasttime=null,
                      registtime=null,
                      realname='',
                      province='',
                      city='',
                      mobile='00000000000',
                      address='',
                      height=172,
                      weight=68,
                      workedat='',
                      post='',
                      hobby='',
                      lookingfor='',
                      introduction='';

update members set lasttime=null,score=100,email='jack@163.com' where ID='jack';

create table pic(path varchar(200)  primary key,
                 IP varchar(15) not null,
                 loadtime timestamp not null,
                 owner varchar(15) not null references members.username,
                 score int null,
                 locked  enum('false','true')  not null  references members.locked
);

create table messages(id          int            primary key auto_increment,
                      tomember    varchar(15)    not null references members.ID,
                      title       varchar(40)    not null,
                      content     varchar(500)   null,
                      time        timestamp      not null,
                      bymember    varchar(15)    not null  references members.ID,
                      byIP        varchar(15)    not null,
                      flag        enum('false','true')  not null
);
create table m_count_to(tomember    varchar(15)   primary key  references members.ID,
                        sex         enum('','Ů')   not null references members.sex,
                        count       int(10)       null
);
create table m_count_by(bymember    varchar(15)   primary key  references members.ID,
                        sex         enum('','Ů')   not null references members.sex,
                        count       int(10)       null
);

create table webinfo(title           varchar(10)    primary key,
                     name            varchar(20)    not null,
                     URL             varchar(200)   not null,
                     address         varchar(100)   not null,
                     coppyright      varchar(100)   not null,
                     version         varchar(100)   not null,
                     telephone       varchar(20)    not null,
                     mobile          varchar(15)    not null,
                     mail            varchar(50)    not null,
                     QQ              varchar(20)    null
);
insert into webinfo set title='վϢ',
                        name='彻',
                        URL='http://ayi411.oicp.net',
                        address='ɳӿ·136',
                        coppyright='Ȩ@޹˾',
                        version='V1.1-20060614',
                        telephone='0411-84208896',
                        mobile='13998519146',
                        mail='ayi_china@163.com',
                        QQ='123456789';
create table member_goodfriends(friend_ID    varchar(15) not null  primary key references members.ID,
                                me_ID   varchar(15)  not null references members.ID,
                                time        timestamp    not null
);
